﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Controls;
using System.ComponentModel;
using System.Reflection;
using System.Collections;

namespace Tools
{
    public class DB
    {
        public string conString;
        SqlCeConnection con;
        public DB(string conString)
        {
            this.conString = conString;
            con = new SqlCeConnection();
            con.ConnectionString = conString;
            con.Open();
        }

        public void Query(string SQLcommand)
        {
            using (SqlCeCommand com = new SqlCeCommand(SQLcommand, con))
            {
                com.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Genenerates a SQL CE compatible CREATE TABLE statement based on a schema obtained from
        /// a SqlDataReader or a SqlCeDataReader.
        /// </summary>
        /// <param name="tableName">The name of the table to be created.</param>
        /// <param name="schema">The schema returned from reader.GetSchemaTable().</param>
        /// <returns>The CREATE TABLE... Statement for the given schema.</returns>
        public string GetCreateTableStatement(DataTable table, string tableName)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Format("CREATE TABLE [{0}] (", tableName));
            
            foreach (DataColumn col in table.Columns)
            {
                
                SqlDbType dbType = GetSqlDBTypeFromType(col.DataType);
                
                builder.Append("[");
                builder.Append(col.ColumnName);
                builder.Append("]");
                builder.Append(" ");
                builder.Append(GetSqlServerCETypeName(dbType, col.MaxLength));
                builder.Append(", ");
            }

            if (table.Columns.Count > 0) builder.Length = builder.Length - 2;

            builder.Append(")");
            return builder.ToString();
        }

        /// <summary>
        /// The method gets the SQL CE type name for use in SQL Statements such as CREATE TABLE
        /// </summary>
        /// <param name="dbType">The SqlDbType to get the type name for</param>
        /// <param name="size">The size where applicable e.g. to create a nchar(n) type where n is the size passed in.</param>
        /// <returns>The SQL CE compatible type for use in SQL Statements</returns>
        public static string GetSqlServerCETypeName(SqlDbType dbType, int size)
        {
            // Conversions according to: http://msdn.microsoft.com/en-us/library/ms173018.aspx
            bool max = (size == int.MaxValue) ? true : false;
            bool over4k = (size > 4000) ? true : false;

            if (size > 0)
            {
                return string.Format(Enum.GetName(typeof(SqlDbType), dbType) + " ({0})", size);
            }
            else
            {
                return Enum.GetName(typeof(SqlDbType), dbType);
            }
        }

        /// <summary>
        /// Gets the correct SqlDBType for a given .NET type. Useful for working with SQL CE.
        /// </summary>
        /// <param name="type">The .Net Type used to find the SqlDBType.</param>
        /// <returns>The correct SqlDbType for the .Net type passed in.</returns>
        public SqlDbType GetSqlDBTypeFromType(Type type)
        {
            TypeConverter tc = TypeDescriptor.GetConverter(typeof(DbType));
            if (/*tc.CanConvertFrom(type)*/ true)
            {
                DbType dbType = (DbType)tc.ConvertFrom(type.Name);
                // A cheat, but the parameter class knows how to map between DbType and SqlDBType.
                SqlCeParameter param = new SqlCeParameter();
                param.DbType = dbType;
                return param.SqlDbType; // The parameter class did the conversion for us!!
            }
            else
            {
                throw new Exception("Cannot get SqlDbType from: " + type.Name);
            }
        }

        public void CreateTableFromDataset(DataSet set)
        {
            SqlCeCommand cmd;
            foreach (DataTable table in set.Tables)
            {
                string createSql = GetCreateTableStatement(table,set.Tables[0].TableName);
                Console.WriteLine(createSql);

                cmd = new SqlCeCommand(createSql, con);
                Console.WriteLine(cmd.ExecuteNonQuery());
            }
        }

        public List<DateTime> QueryGetDateTimeleList(string query)
        {
            List<DateTime> polozky = new List<DateTime>();
            try
            {

                using (SqlCeCommand com = new SqlCeCommand(query, con))
                {
                    SqlCeDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        polozky.Add(reader.GetDateTime(0));
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return polozky;
        }


        public void AddRow(string tableName, DataRow dataRow)
        {
            // Create DataAdapter
            //
            SqlCeDataAdapter adp = new SqlCeDataAdapter(
                "SELECT * FROM Nevracene", con);

            SqlCeCommandBuilder cb = new SqlCeCommandBuilder(adp);

            // Create and fill the dataset (select only first 5 rows)
            //
            DataSet ds = new DataSet();
            adp.Fill(ds, 0, 0, "Table");

            DataTable table = ds.Tables["Table"];
          //  DataRow datarow = table.NewRow();
          //  datarow["jmeno"] = "teeeest";
            table.Rows.Add(dataRow);


            // Update, this operation fires two events (RowUpdating/RowUpdated)  
            //
            adp.Update(ds, "Table");

            // Remove handlers
            //

        }

    /*    public void GetqlDataAdapter(string tableName)
        {
           return SqlCeDataAdapter adp = new SqlCeDataAdapter(
            "SELECT * FROM " + tableName, con);

            SqlCeCommandBuilder cb = new SqlCeCommandBuilder(adp);
            DataSet ds = new DataSet();
            adp.Fill(ds, 0, 0, "Table");

            DataTable table = ds.Tables["Table"];
        }*/

        public DataRow GetRowEmpty(string tableName)
        {
            SqlCeDataAdapter adp = new SqlCeDataAdapter(
               "SELECT * FROM " + tableName, con);

            SqlCeCommandBuilder cb = new SqlCeCommandBuilder(adp);

            // Create and fill the dataset (select only first 5 rows)
            //
            DataSet ds = new DataSet();
            adp.Fill(ds, 0, 0, "Table");

            DataTable table = ds.Tables["Table"];
            return table.NewRow(); 
        }

       /* public DataRow QueryGetRow(string command)
        {
            /*SqlCeDataAdapter adp = new SqlCeDataAdapter(
               "SELECT * FROM " + tableName, con);

            SqlCeCommandBuilder cb = new SqlCeCommandBuilder(adp);

            // Create and fill the dataset (select only first 5 rows)
            //
            DataSet ds = new DataSet();
            adp.Fill(ds, 0, 0, "Table");

            DataTable table = ds.Tables["Table"];
            return table.NewRow();
            return "-1";
        }*/


        public void AddRow(string table, Dictionary<string, string> polozka)
        {
            string values = "";
            string colums = "";

            List<string> list = new List<string>(polozka.Keys);

            for (int i = 0; i < list.Count - 1; i++)
            {
                colums += list[i] + ",";
                string datetime = "";
                DateTime datetimeresult;
                if (DateTime.TryParse(polozka[list[i]].ToString(), out datetimeresult))
                {
                    values += "'" + datetimeresult.ToString("yyyy-MM-dd hh:mm:ss") + "'" + ",";
                }
                else
                {
                    values += "'" + polozka[list[i]].ToString() + "'" + ",";
                }
            }

            colums += list[list.Count - 1];
            values += "'" + polozka[list[list.Count - 1].ToString()] + "'";

            string
            command = "INSERT INTO " + table;
            command += " (" + colums + ")";
            command += " VALUES ";
          
            command += " (" + values + ")";

            using (SqlCeCommand com = new SqlCeCommand(command, con))
            {
                com.ExecuteNonQuery();
            }
        }



        public static object SetValuesToObject(Type tClass, DataRow dr)
        {
            try
            {
                Object oClass = System.Activator.CreateInstance(tClass);
                MemberInfo[] methods = tClass.GetMethods();
                ArrayList aMethods = new ArrayList();
                object[] aoParam = new object[1];

                //Get simple SET methods
                foreach (MethodInfo method in methods)
                {
                    if (method.DeclaringType == tClass && method.Name.StartsWith("set_"))
                        aMethods.Add(method);
                }

                //Invoke each set method with mapped value
                for (int i = 0; i < aMethods.Count; i++)
                {
                    try
                    {
                        MethodInfo mInvoke = (MethodInfo)aMethods[i];
                        //Remove "set_" from method name
                        string sColumn = mInvoke.Name.Remove(0, 4);
                        //If row contains value for method...
                        if (dr.Table.Columns.Contains(sColumn))
                        {
                            //Get the parameter (always one for a set property)
                            ParameterInfo[] api = mInvoke.GetParameters();
                            ParameterInfo pi = api[0];

                            //Convert value to parameter type
                            aoParam[0] = Convert.ChangeType(dr[sColumn], pi.ParameterType);

                            //Invoke the method
                            mInvoke.Invoke(oClass, aoParam);
                        }
                    }
                    catch
                    {
                        System.Diagnostics.Debug.Assert(false, "SetValuesToObject failed to set a value to an object");
                    }
                }
                return oClass;
            }
            catch
            {
                System.Diagnostics.Debug.Assert(false, "SetValuesToObject failed to create an object");
                return null;
            }
        } 


        public void SearechSortAndShow(string table, Dictionary<string, string> polozky, ListView listViw)
        {
            if (polozky.Count > 0)
            {
                List<string> list = new List<string>(polozky.Keys);
                string arguments = "";
                for (int i = 0; i < list.Count - 1; i++)
                {
                    if ("idObjednavky" == list[i])
                    {
                        if (polozky[list[i]] != "")
                        {
                            arguments += list[i] + " LIKE ";
                            arguments += "'" + polozky[list[i]] + "'";
                            arguments += " AND ";
                        }
                    }
                    else if (polozky[list[i]] != "")
                    {
                        arguments += list[i] + " LIKE ";
                        arguments += "'" + polozky[list[i]] + "%'";
                        arguments += " AND ";
                    }
                }
                    arguments += list[list.Count - 1] + " LIKE ";
                    arguments += "'" + polozky[list[list.Count - 1]] + "%'";
                

                SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM " + table + " WHERE " + arguments + " ORDER BY id DESC;", con);
                DataSet data = new DataSet();
                adapter.Fill(data);
                listViw.DataContext = data.Tables[0].DefaultView;
            }
            else
            {
                loadAllSortTo("id", "Nevracene", listViw);
            }
        }

        public DataTable GetDataTable(string table)
        {
            var cmd = new SqlCeCommand("SELECT * FROM "+table, con);
            var dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            return dt;
        }

        public void CloneTable(string originalTable, string newTable)
        {
            DataTable ss = GetDataTable(originalTable);
            string tableString = GetCreateTableStatement(ss, newTable);
            Query(tableString);
        }
        

        public bool QueryGetBool(string prikaz)
        {
            bool polozka = false;
            using (SqlCeCommand com = new SqlCeCommand(prikaz, con))
            {
                SqlCeDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    polozka = reader.GetBoolean(0);
                }
            }
            return polozka;
        }

        public int GetRowsCount(string table)
        {
            Int32 count = 0;
            string command = "SELECT COUNT(*) FROM " + table;
            using (SqlCeCommand com = new SqlCeCommand(command, con))
            {
                count = (Int32)com.ExecuteScalar();
            }
            return count;
        }

        public string QueryGetString(string prikaz)
        {
            string polozka = "";
            using (SqlCeCommand com = new SqlCeCommand(prikaz, con))
            {
                SqlCeDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    polozka = reader.GetString(0);
                }
                return polozka;
            }
        }


        public List<string> QueryGetListString(string command)
        {
            {
                List<string> polozky = new List<string>();
                using (SqlCeCommand com = new SqlCeCommand(command, con))
                {
                    SqlCeDataReader reader = com.ExecuteReader();
                    while (reader.Read())

                        polozky.Add(reader.GetString(0));
                }
                return polozky;
            }
        }

        public List<int> QueryGetListInt(string command)
        {
            {
                List<int> polozky = new List<int>();
                using (SqlCeCommand com = new SqlCeCommand(command, con))
                {
                    SqlCeDataReader reader = com.ExecuteReader();
                    while (reader.Read())

                        polozky.Add(reader.GetInt32(0));
                }
                return polozky;
            }
        }

        public int QueryGetInt(string command)
        {
            int count = 0;
            try
            {
                using (SqlCeCommand com = new SqlCeCommand(command, con))
                {
                    SqlCeDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        count = reader.GetInt32(0);
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
            }
          
            return count;
        }



        public void UpdateTable(DataTable table, string tableName)
        {
            SqlCeConnection mSqlConnection = new SqlCeConnection(conString);
            String mQueryForSqlDataAdapter = "Select * from Ceny";
            SqlCeDataAdapter tmpSqlDataAdapter;
            SqlCeCommandBuilder tmpSqlCommandBuilder;
            DataTable tmpDataTable = new DataTable();
            tmpSqlDataAdapter = new SqlCeDataAdapter(mQueryForSqlDataAdapter, mSqlConnection);

            tmpDataTable = tmpSqlDataAdapter.FillSchema(tmpDataTable, SchemaType.Mapped);
            tmpSqlCommandBuilder = new SqlCeCommandBuilder(tmpSqlDataAdapter);
            tmpSqlDataAdapter.Update(table);

            /* using (SqlCeConnection con = new SqlCeConnection(conString))
               {
                   con.Open();
                   SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM " + tableName, con);
                   DataSet data = new DataSet();
              
                   adapter.FillSchema(data, SchemaType.Source, "Ceny");
                   adapter.Fill(data,tableName);
                   table.TableName = tableName;
                  // data.Tables.Remove(tableName);
                  // data.Tables.Add(table);

                   adapter.Update(data);
                   con.Close();
               }*/
        }

        public Dictionary<string, string> GetRow(string table, string id)
        {
            Dictionary<string, string> row = new Dictionary<string, string>();
            string command = "SELECT * FROM " + table + " WHERE id = '" + id + "'";
            using (SqlCeCommand com = new SqlCeCommand(command, con))
            {
                SqlCeDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row.Add(reader.GetName(i), reader[i].ToString());
                    }
                }
            }
            return row;
        }

        public void UpdateRow(Dictionary<string, string> polozka, string table, string podminka)
        {
            string comm = "UPDATE " + table + " SET ";
            List<string> list = new List<string>(polozka.Keys);
            for (int i = 0; i < polozka.Count - 1; i++)
            {
                comm += list[i] + "='" + polozka[list[i]].ToString() + "' ,";
            }
            comm += list[list.Count - 1] + "='" + polozka[list[list.Count - 1]].ToString() + "' ";
            comm += podminka;

            using (SqlCeCommand com = new SqlCeCommand(comm, con))
            {
                com.ExecuteNonQuery();
            }
        }

        public Dictionary<string, string> QueryGetRow(string command)
        {
            Dictionary<string, string> row = new Dictionary<string, string>();
            using (SqlCeCommand com = new SqlCeCommand(command, con))
            {
                SqlCeDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row.Add(reader.GetName(i), reader[i].ToString());
                    }
                }
            }
            return row;
        }



        public void DeleteRow(string table, string col, string arg)
        {
            using (SqlCeCommand com = new SqlCeCommand("DELETE FROM " + table + " WHERE " + col + " = '" + arg + "' ;", con))
            {
                com.ExecuteNonQuery();
            }
        }

        public void loadAllSortTo(string col, string table, ListView listViw)
        {
            SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM " + table + " ORDER BY " + col + " DESC;", con);
            DataSet data = new DataSet();
            adapter.Fill(data);
            listViw.DataContext = data.Tables[0].DefaultView;
        }

        public bool ExistTable(string table)
        {
            bool polozky = false;
            string command = "SELECT COUNT(table_name) FROM information_schema.tables WHERE table_name = '" + table + "'";

            using (SqlCeCommand com = new SqlCeCommand(command, con))
            {
                SqlCeDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    int result = reader.GetInt32(0);
                    if (result >= 1)
                        polozky = true;
                }
            }
            return polozky;
        }
    }
}